import { query, insert, update, remove } from "../db";

/**
 * 分类相关的数据库操作
 */
export class CategoryService {
  /**
   * 获取所有分类
   */
  static async getAllCategories() {
    return await query(`
        SELECT 
          c.id,
          c.name,
          c.description,
          c.created_at AS createdAt,
          c.updated_at AS updatedAt,
          c.cover_url AS coverUrl,
          COUNT(v.id) AS videoCount
        FROM categories c
        LEFT JOIN videos v ON c.id = v.category
        GROUP BY c.id
        ORDER BY c.name
      `);
  }

  static async getCategoryById(id: string) {
    return await query(
      `
        SELECT 
          id,
          name,
          cover_url AS coverUrl,
          description,
          video_count AS videoCount,
          created_at AS createdAt,
          updated_at AS updatedAt
        FROM categories WHERE id = ?
      `,
      [id]
    );
  }

  /**
   * 添加分类
   */
  static async addCategory(name: string, description?: string) {
    return await insert(
      `
        INSERT INTO categories (name, description) VALUES (?, ?)
      `,
      [name, description]
    );
  }

  /**
   * 更新分类
   */
  static async updateCategory(id: string, name: string, description?: string) {
    return await update(
      `
        UPDATE categories SET name = ?, description = ? WHERE id = ?
      `,
      [name, description, id]
    );
  }

  /**
   * 删除分类
   */
  static async deleteCategory(id: string) {
    return await remove(
      `
        DELETE FROM categories WHERE id = ?
      `,
      [id]
    );
  }
}
